package ig.metrica.origenesdedatos;

import ig.metrica.modelos.entity.ModeloorigenesdatosDTO;
import ig.util.ConexionServer;
import ig.util.ManipulacionConsultas;
import ig.util.ejb.IGBean;
import ig.util.ejb.IGEntityBean;
import ig.util.excepciones.DatoInvalidoExcepcion;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Properties;
import java.util.ArrayList;
import javax.ejb.CreateException;
import javax.ejb.FinderException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class OrigenDeDatosBean extends IGEntityBean implements OrigenDeDatosBussines {
    
    private ig.metrica.modelos.SessionModelosRemote sessionRemote=null;
    private ig.metrica.modelos.SessionModelosRemoteHome SShome=null;
    
    private String entidad;
    private String campos;
    private String query;
    private Integer consecutivo;
    private ConexionPK conexionPK;
    private Integer id;

    private boolean select;

    public boolean isSelect() {
        return select;
    }

    public void setSelect(boolean select) {
        this.select = select;
    } 
   
//        private boolean store=false;
    
    public String getEntidad(){
        return entidad;
    }
    
    public Integer getConsecutivo() {
        return consecutivo;
    }
    
    public Integer getId() {
        return id;
    }
    
    public void setId(Integer id) {
        this.id = id;
    }
    
    public String getQuery(){
        return query;
    }
    
    public void setQuery(String query){
        this.query = query;
//                store=true;
    }
    
    public ConexionPK getConexionPK(){
        return conexionPK;
    }
    
    public void setCampos(String campos){
        this.campos = campos;
//                store=true;
    }
    
    public void setEntidad(String entidad) {
        this.entidad = entidad;
//        store=true;
    }
    
    public void setConsecutivo(Integer consecutivo) {
        this.consecutivo = consecutivo;
//        store=true;
    }
    
    public String getCampos(){
        return campos;
    }
    
    /*public ArrayList getArregloCampos(){
        ArrayList arreglo = new ArrayList();
        String[] tokens = campos.split(",");
        for (int i = 0; i<tokens.length; i++){
            String campo = tokens[i].trim();
            arreglo.add( campo );
        }
        return arreglo;
    }*/
    
    
    public void setConexionPK(ConexionPK pk){
        this.conexionPK = pk;
//                store=true;
    }
    
    private Hashtable<String,Connection> PoolConnection= new  Hashtable<String,Connection>();
    private Connection getConnection(String URL,String usuario,String password) throws SQLException{
        String key=URL.trim()+usuario.trim()+password.trim();
        Connection connection =PoolConnection.get(key);
        if(connection==null || connection.isClosed()){
            try {
                connection =DriverManager.getConnection(URL.trim(), usuario.trim(), password.trim());
                PoolConnection.put(key,connection);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return connection;
    }
    public void closePoolConnection() {
        //System.out.println("[CONTAINER] :Closing "+PoolConnection.size()+" Connection of Pool Connection.");
        //Enumeration<Connection> element=PoolConnection.elements();
        Enumeration<String> key=PoolConnection.keys();
        while(key.hasMoreElements()){
            try {
                String KEY=key.nextElement();
                Connection c=PoolConnection.get(KEY);
                if(c!=null && !c.isClosed())
                    c.close();
                PoolConnection.remove(KEY);
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
//       System.out.println("[CONTAINER] :Clos Pool Connection:"+PoolConnection.size()+" Connection.");
    }
    
    public ArrayList obtenerDatos(String query) throws NamingException, FinderException, ClassNotFoundException, SQLException{
        
        ArrayList resultados = new ArrayList();
        InitialContext _ctx = new InitialContext();
        Connection connection =null;
        PreparedStatement p =null;
        ResultSet r=null;
        try {
            ConexionHome conHome = (ConexionHome)_ctx.lookup("ig.metrica.origenesdedatos.Conexion");
            Conexion conexion = conHome.findByPrimaryKey( conexionPK );
            Class.forName( conexion.getDriver() );
            connection = this.getConnection(conexion.getUrl(), conexion.getUsuario(), conexion.getPassword());
            try {
                //return  new ManipulacionConsultas().ejecutarConsulta(connection,query);

                //Cambio por Cristhiam Reina
                //30/08/2011
                //Pruebas Eliecer
                
                ArrayList ret = new ManipulacionConsultas().ejecutarConsulta(connection,query);

                System.out.println("Consulta ejecutada : " );
                System.out.println("Numero de registros de la consulta: " + ret.size());
                System.out.println(query);
                System.out.println("");


                return  ret;
       
            } catch (Exception ex) {
                throw new SQLException(ex.toString());
            }
        } catch (RemoteException e) {
            e.printStackTrace();
            throw new NamingException(e.toString());
        }finally {
            try {r.close();} catch (Exception e) {}
            try {p.close();} catch (Exception e) {}
            try {connection.close();} catch (Exception e) {}
        }
//        return resultados;
    }
    public void ejbPostCreate(java.lang.Integer id, java.lang.String entidad, java.lang.String campos, java.lang.String query, java.lang.Integer idconexion,java.lang.Integer consecutivo) throws CreateException, RemoteException{
        
    }
    public OrigenDeDatosPK ejbCreate(java.lang.Integer id, java.lang.String entidad, java.lang.String campos, java.lang.String query, java.lang.Integer idconexion, java.lang.Integer _consecutivo) throws CreateException, RemoteException{
        Connection _conn = null;
        try {
            _conn = getConexion("METRICADB");
            pstmt1  = _conn.prepareStatement("SELECT CONSECUTIVO FROM ORIGENESDATOS WHERE CONSECUTIVO=?");
            int i=1;
            if(id>=3000)
                i=3001;            
            else if(id>=2000)
                i=2001;
            else if(id>=1000)
                i=1001;
            while(true){
                pstmt1.clearParameters();
                pstmt1.setInt(1,i);
                ResultSet r =pstmt1.executeQuery();
                if(r.next()){
                    i++;
                }else
                    break;
            }
            _consecutivo=i;
            if(_consecutivo>=2000 && _consecutivo<=2999){
                id=_consecutivo;
                campos="S"+campos.substring(1,campos.length());
            }
            
            this.entidad=entidad;
            this.campos=campos;
            this.query=query;
            this.consecutivo=_consecutivo;
            this.id=id;
            this.setConexionPK(new ConexionPK(idconexion));
            
            pstmt1 = _conn.prepareStatement("INSERT INTO ORIGENESDATOS (ID, ENTIDAD, CAMPOS, QUERY, IDCONEXION, CONSECUTIVO) VALUES(?,?,?,?,?,?)");
            pstmt1 .setInt(1,id);
            pstmt1 .setString(2,entidad);
            pstmt1 .setString(3,campos);
            pstmt1 .setString(4,query);
            pstmt1 .setInt(5,idconexion);
            pstmt1 .setInt(6,_consecutivo);
            pstmt1 .execute();
            
            return new OrigenDeDatosPK(_consecutivo);
        } catch (Exception e) {
            e.printStackTrace();
            throw new CreateException(e.toString());
        }finally{
            try {
                pstmt1.close();
            } catch (Exception e) {}
            
            try {
                pstmt2.close();
            } catch (Exception e) {}
            
            try {
                pstmt3.close();
            } catch (Exception e) {}
            
            try {
                _conn.close();
            } catch (Exception e) {}
            
        }
    }
    public Collection ejbFindByAll() throws FinderException{
        Collection todos = new ArrayList();
        Connection _conn = null;
        Statement s=null;
        ResultSet r=null;
        try {
            _conn = getConexion("METRICADB");
            s = _conn.createStatement();
            r = s.executeQuery("SELECT ID, ENTIDAD, CAMPOS, QUERY, IDCONEXION, CONSECUTIVO FROM ORIGENESDATOS ORDER BY ID"/*ENTIDAD,CONSECUTIVO*/);
            while(r.next()) {
                OrigenDeDatosPK pk = new  OrigenDeDatosPK( r.getInt("CONSECUTIVO"));
                todos.add( pk );
            }
        } catch (Exception e) {
            System.out.println("Error: \n"+e);
        } finally {
            try {_conn.close();} catch (Exception e) {}
            try {s.close();} catch (Exception e) {}
            try {r.close();} catch (Exception e) {}
        }
        return todos;
    }
    
    public Collection findByAllDTO() throws FinderException{
        Collection<OrigenesdatosDTO> collection = new ArrayList<OrigenesdatosDTO>();
        Connection _conn = null;
        Statement s=null;
        ResultSet r = null;
        try {
            _conn = getConexion("METRICADB");
            s = _conn.createStatement();
            r = s.executeQuery("SELECT ID, ENTIDAD, CAMPOS, QUERY, IDCONEXION, CONSECUTIVO FROM ORIGENESDATOS ORDER BY ENTIDAD");
            while(r.next()) {
                OrigenesdatosDTO origenesdatosDTO = new  OrigenesdatosDTO();
                origenesdatosDTO.setId(r.getInt("ID"));
                origenesdatosDTO.setEntidad(r.getString("ENTIDAD"));
                origenesdatosDTO.setCampos(r.getString("CAMPOS"));
                origenesdatosDTO.setQuery(r.getString("QUERY"));
                origenesdatosDTO.setIdconexion(r.getInt("IDCONEXION"));
                origenesdatosDTO.setConsecutivo(r.getInt("CONSECUTIVO"));
                collection .add(origenesdatosDTO);
            }
        } catch (Exception e) {
            System.out.println("Error: \n"+e);
        } finally {
            try {_conn.close();} catch (Exception e) {}
            try {r.close();} catch (Exception e) {}
            try {s.close();} catch (Exception e) {}
        }
        return collection;
    }
    
    public ArrayList obtenerDatos(String query,String driver,String URL,String user,String pass) throws NamingException, FinderException, ClassNotFoundException, SQLException{
        ArrayList resultados = null;
        Connection connection =null;
        PreparedStatement p =null;
        ResultSet r=null;
        try {
            Class.forName(driver);
            connection = this.getConnection(URL.trim(),user,pass);
            try {
                resultados = new ManipulacionConsultas().ejecutarConsulta(connection,query);
                String[] columnas= new ManipulacionConsultas().getColumName(connection,query);
                resultados.add(columnas);
                return resultados;
            } catch (Exception ex) {
                throw new SQLException(ex.toString());
            }
        }finally {
            try {r.close();} catch (Exception e) {}
            try {p.close();} catch (Exception e) {}
            try {connection.close();} catch (Exception e) {}
        }
    }
    
    public boolean  eliminarConsulta(java.lang.Integer consecutivo)throws NamingException, FinderException, ClassNotFoundException, SQLException, RemoteException,Exception{
        Connection _conn = null;
        if(!validarEnModelos(consecutivo)){
            try {
                _conn = getConexion("METRICADB");
                pstmt1 = _conn.prepareStatement("DELETE FROM ORIGENESDATOS WHERE CONSECUTIVO=?");
                pstmt1.setInt(1,consecutivo);
                pstmt1.execute();
                return true;
            } catch (SQLException ex) {
                _conn.close();
                throw ex;
            } catch (NamingException ex) {
                throw ex;
            }finally {
                try{pstmt1.close();} catch (Exception e) {}
                try{_conn.close();} catch (Exception e) {}
            }
        }
        throw new Exception("No se pudo eliminimar la Consulta porque tiene una relaci\u00F3n con un modelo\nelimine primero la relaci\u00F3n con el modelo.");
        //return false;
    }
    
    private boolean validarEnModelos(Integer consecutivo)throws SQLException,NamingException {
        Connection _conn = null;
        ResultSet r = null;
        try {
            _conn = getConexion("METRICADW");
            pstmt1 = _conn.prepareStatement("SELECT MODELO FROM MODELOORIGENESDATOS WHERE ORIGENESDATOS=?");
            pstmt1.setInt(1, consecutivo);
            r = pstmt1.executeQuery();
            if(r.next())
                return true;
        } catch (SQLException ex) {
            throw ex;
        } catch (NamingException ex) {
            throw ex;
        } finally {
            try{r.close();} catch (Exception e) {}
            try{_conn.close();} catch (Exception e) {}
            try{_conn.close();} catch (Exception e) {}
        }
        return false;
    }
    
    public boolean ejecutarQuery(String query,String datasource) throws NamingException, FinderException, ClassNotFoundException, SQLException{
        boolean Res=false;
        Connection _conn = null;
        Statement st =null;
        try {
            _conn = getConexion(datasource);
            st = _conn.createStatement();
            Res=st.execute(query);
        } catch (Exception e) {
            throw new SQLException(e.toString());
        } finally {
            try{st.close();} catch (Exception e) {}
            try{_conn.close();} catch (Exception e) {}
        }
        return Res;
    }
    
    public Collection obtenerResultado(String query) throws NamingException, FinderException, ClassNotFoundException, SQLException{
        ArrayList resultados = new ArrayList();
        InitialContext ctx = new InitialContext();
        PreparedStatement p = null;
        ResultSet r =null;
        Connection _conn = null;
        try {
            ConexionHome conHome = (ConexionHome)ctx.lookup("ig.metrica.origenesdedatos.Conexion");
            Conexion conexion = conHome.findByPrimaryKey( conexionPK );
            Class.forName( conexion.getDriver() );
            _conn = this.getConnection(conexion.getUrl(), conexion.getUsuario(), conexion.getPassword());
            p = _conn.prepareStatement( query );
            r = p.executeQuery();
            int numCols = r.getMetaData().getColumnCount();
            while(r.next()){
                Object[] registro = new Object[numCols];
                for (int i=0; i<numCols; i++){
                    String Nom = r.getMetaData().getColumnName(i+1);
                    registro[i] = r.getObject(i+1);
                }
                resultados.add( registro );
            }
        } catch (Exception e) {
            resultados =null;
        } finally {
            try{r.close();} catch (Exception e) {}
            try{p.close();} catch (Exception e) {}
            try{_conn.close();} catch (Exception e) {}
        }
        return resultados;
    }
    
    public ArrayList obtenerDatos(Hashtable obj) throws NamingException, FinderException, ClassNotFoundException, SQLException{
        ArrayList arrayList =new ArrayList();
        boolean obtenerDatos=false;
        Collection c=new ArrayList();
        Integer idModelo=null;
        try {
            SShome=(ig.metrica.modelos.SessionModelosRemoteHome)getContex().lookup("SessionModelosBean");
            sessionRemote=SShome.create();
            try {
                idModelo=Integer.valueOf(obj.get("IDMODELO").toString());
                c=sessionRemote.findByModelo(idModelo);
            } catch (Exception e) {
                c=sessionRemote.findAll();
            }
            for (Object elem : c) {
                if(((ModeloorigenesdatosDTO)elem).getOrigenesdatos().equals(this.getConsecutivo())){
                    if(idModelo!=null && ((ModeloorigenesdatosDTO)elem).getModelo().equals(idModelo)){
                        arrayList = obtenerDatos( query );
                        break;
                    }
                }
            }
//            store=false;//
        } catch (RemoteException ex) {
            ex.printStackTrace();
        } catch (CreateException ex) {
            ex.printStackTrace();
        }
        return arrayList;
    }
    
    InitialContext ctxx=null;
    private InitialContext getContex(){
        if(ctxx==null){
            Properties prop = new Properties();
            prop.put( Context.INITIAL_CONTEXT_FACTORY, IGBean.INITIAL_CONTEXT_FACTORY);
            String Sockec = new ConexionServer().getSocketServer();
            prop.put( Context.PROVIDER_URL, Sockec);
            try {
                ctxx= new InitialContext(prop);
            } catch (NamingException ex) {
                ex.printStackTrace();
            }
        }
        return ctxx;
    }
    
    public void cargarEntidad() throws NamingException, SQLException{
        Connection _conn = null;
        ResultSet r =null;
        try {
            OrigenDeDatosPK pk = (OrigenDeDatosPK)ctx.getPrimaryKey();
            long idInterna = pk.getIdInterna();
            _conn = getConexion("METRICADB");
            pstmt1 = _conn.prepareStatement("SELECT ID, ENTIDAD, CAMPOS, QUERY, IDCONEXION, CONSECUTIVO FROM ORIGENESDATOS WHERE CONSECUTIVO=?");
            pstmt1.setLong(1, idInterna);
            
            r = pstmt1.executeQuery();
            r.next();
            
            this.id=r.getInt("ID");
            this.consecutivo= r.getInt("CONSECUTIVO");
            this.entidad = r.getString("ENTIDAD");
            this.campos = r.getString("CAMPOS");
            this.query = r.getString("QUERY");
            
            if (query==null || query.equals("") || query.length()==0)
                query = "";
            this.conexionPK = new ConexionPK( (new Integer(r.getInt("IDCONEXION"))).intValue() );
//                store=true;
        } catch (SQLException e) {
            throw e;
        } catch (NamingException e) {
            throw e;
        }finally{
            try{r.close();} catch (Exception e) {}
            try{pstmt1.close();} catch (Exception e) {}
            try{_conn.close();} catch (Exception e) {}
        }
    }
    
    public void actualizarEntidad() throws DatoInvalidoExcepcion, NamingException, SQLException{
//      if(store){
        Connection _conn = null;
        try {
            if ( entidad.trim().equals("") || conexionPK==null ){
                throw new DatoInvalidoExcepcion("Alguna de las propiedades de la conexion posee un valor invalido.");
            }
            //System.out.println("STORE.."+entidad);
            OrigenDeDatosPK pk = (OrigenDeDatosPK)ctx.getPrimaryKey();
            long idInterna = pk.getIdInterna();
            _conn = getConexion("METRICADB");
            
            pstmt1 = _conn.prepareStatement("UPDATE ORIGENESDATOS SET ENTIDAD=?, QUERY=?, IDCONEXION=?, CAMPOS=? WHERE CONSECUTIVO=?");
            pstmt1.setString(1, entidad);
            pstmt1.setString(2, query);
            pstmt1.setLong(3, conexionPK.getIdInterna());
            pstmt1.setString(4, campos );
            pstmt1.setLong(5, idInterna);
            pstmt1.executeUpdate();
            
        } catch (DatoInvalidoExcepcion e) {
            System.out.println("entidad:"+entidad);
            e.printStackTrace();
            throw e;
        } catch (NamingException e) {
            System.out.println("entidad:"+entidad);
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            System.out.println("entidad:"+entidad);
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            System.out.println("entidad:"+entidad);
            e.printStackTrace();
            throw new DatoInvalidoExcepcion(e.toString());
        }finally {
            try{_conn.close();} catch (Exception e) {}
            try{pstmt1.close();} catch (Exception e) {}
        }
    }
    
    public void eliminarEntidad(){}
    
    public OrigenDeDatosPK ejbFindByPrimaryKey(OrigenDeDatosPK pk) throws FinderException{
        Connection _conn = null;
        ResultSet r = null;
        try {
            _conn = getConexion("METRICADB");
            pstmt1 = _conn.prepareStatement("SELECT ID FROM ORIGENESDATOS WHERE CONSECUTIVO=?");
            boolean salir=false;
            do{
                pstmt1.setLong(1, pk.getIdInterna());
                r = pstmt1.executeQuery();
                salir=r.next();
            }while(!salir);
            return pk;
        } catch (Exception e) {
            throw new FinderException(e.toString());
        } finally{
            try{r.close();} catch (Exception e) {}
            try{pstmt1.close();} catch (Exception e) {}
            try{_conn.close();} catch (Exception e) {}
        }
    }
    
    public OrigenDeDatosPK ejbFindByNombreEntidad(String entidad) throws FinderException{
        Connection _conn = null;
        ResultSet r = null;
        try {
            _conn = getConexion("METRICADB");
            pstmt1 = _conn.prepareStatement("SELECT CONSECUTIVO FROM ORIGENESDATOS WHERE ENTIDAD=?");
            pstmt1.setString(1, entidad );
            r = pstmt1.executeQuery();
            if (r.next()==false)
                throw new FinderException("No fue posible encontrar el origen de datos: " + entidad );
            return new OrigenDeDatosPK( r.getInt("CONSECUTIVO") );
        } catch (Exception e) {
            throw new FinderException(e.toString());
        } finally{
            try{r.close();} catch (Exception e) {}
            try{pstmt1.close();} catch (Exception e) {}
            try{_conn.close();} catch (Exception e) {}
        }
    }

    @Override
    public String toString() {
        return "";
    }
    
}
